package com.sanqi.weixin.service.impl;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import com.alibaba.fastjson2.JSONObject;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.sanqi.common.utils.DateUtils;
import com.sanqi.common.utils.PoiExcelUtil;
import com.sanqi.common.utils.SQMapUtil;
import com.sanqi.common.utils.SQStringUtil;
import com.sanqi.weixin.domain.WxFile;
import com.sanqi.weixin.mapper.WxFileMapper;
import com.sanqi.weixin.pojo.UserWorkInfo;
import com.sanqi.weixin.service.IWxFileService;
import com.sanqi.weixin.util.WxConstants;
import com.sanqi.work.domain.WorkScoreConf;
import com.sanqi.work.domain.WorkStepLevel;
import com.sanqi.work.domain.WorkStepLevelPrice;
import com.sanqi.work.domain.WorkUser;
import com.sanqi.work.domain.WorkUserAccount;
import com.sanqi.work.manager.IWorkScoreConfManager;
import com.sanqi.work.manager.IWorkStepLevelPriceManager;
import com.sanqi.work.manager.IWorkUserAccountManager;
import com.sanqi.work.manager.IWorkUserManager;
import com.sanqi.work.mongodomain.WorkExcelInfo;
import com.sanqi.work.mongomanager.IWorkExcelInfoManager;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.map.MapUtil;
import cn.hutool.core.util.IdUtil;
import cn.hutool.core.util.StrUtil;
import lombok.extern.slf4j.Slf4j;

/**
 * 导入文件Service业务层处理
 * 
 * @author duany
 * @date 2025-09-24
 */
@Service
@Slf4j
public class WxFileServiceImpl extends ServiceImpl<WxFileMapper, WxFile> implements IWxFileService 
{
    @Value("${sanqi.excelFilePath}")
    private String excelFilePath;
    
    @Autowired
    private WxFileMapper wxFileMapper;
    
    @Autowired
    private IWorkExcelInfoManager excelInfoManager;
    
    @Autowired
    private IWorkUserManager workUserManager;
    
    @Autowired
    private IWorkUserAccountManager workUserAccountManager;
    
    @Autowired
    private IWorkStepLevelPriceManager workStepLevelPriceManager;
    
    @Autowired
    private IWorkScoreConfManager workIWorkScoreConfManager;
    
    private String m2m3Code = "hubei_base_volume_m2m3";
    private String m4Code = "hubei_base_volume_m4";

    /**
     * 查询导入文件
     * 
     * @param id 导入文件主键
     * @return 导入文件
     */
    @Override
    public WxFile selectWxFileById(String id)
    {
        return wxFileMapper.selectWxFileById(id);
    }

    /**
     * 查询导入文件列表
     * 
     * @param wxFile 导入文件
     * @return 导入文件
     */
    @Override
    public List<WxFile> selectWxFileList(WxFile wxFile)
    {
        return wxFileMapper.selectWxFileList(wxFile);
    }

    /**
     * 新增导入文件
     * 
     * @param wxFile 导入文件
     * @return 结果
     */
    @Override
    public int insertWxFile(WxFile wxFile)
    {
        wxFile.setCreateTime(DateUtils.getNowDate());
        return wxFileMapper.insertWxFile(wxFile);
    }

    /**
     * 修改导入文件
     * 
     * @param wxFile 导入文件
     * @return 结果
     */
    @Override
    public int updateWxFile(WxFile wxFile)
    {
        wxFile.setUpdateTime(DateUtils.getNowDate());
        return wxFileMapper.updateWxFile(wxFile);
    }

    /**
     * 批量删除导入文件
     * 
     * @param ids 需要删除的导入文件主键
     * @return 结果
     */
    @Override
    public int deleteWxFileByIds(String[] ids)
    {
        return wxFileMapper.deleteWxFileByIds(ids);
    }

    /**
     * 删除导入文件信息
     * 
     * @param id 导入文件主键
     * @return 结果
     */
    @Override
    public int deleteWxFileById(String id)
    {
        return wxFileMapper.deleteWxFileById(id);
    }

    @Override
    public WxFile saveFile(MultipartFile file, String taskNo, String workplaceId) {
        //获取文件夹
        File dir = FileUtil.mkdir(excelFilePath);
        //保存原始文件
        String filePath = dir.getPath() + File.separator + SQStringUtil.getSnowflakeStr() + File.separator + file.getOriginalFilename();
        WxFile wxFile = new WxFile();
        wxFile.setWorkplaceId(workplaceId);
        wxFile.setFileName(file.getOriginalFilename());
        wxFile.setFileStatus(WxConstants.FILE_STATUS_IN);
        wxFile.setFilePath(filePath);
        wxFile.setFileType("1");
        wxFile.setTaskNo(taskNo);
        
        try {
            file.transferTo(new File (filePath));
            save(wxFile);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            wxFile.setSendException(SQStringUtil.getExceptionString(e));
        }
        
        return wxFile;
    }

    @Override
    public boolean dealwithFile(String id) {
        WxFile wxFile = this.getById(id);
        if(null == wxFile) {
            return false;
        }
        
        FileOutputStream out = null;
        String sheetName = null;
        try {
            //获取文件
            String filePath = wxFile.getFilePath();
            String ext = FileUtil.extName(filePath);
            File file = FileUtil.file(filePath);
            String parentPath = FileUtil.getParent(filePath, 1);
            String newFilePath = parentPath + FileUtil.FILE_SEPARATOR + SQStringUtil.getSnowflakeStr() + "." + ext;
            out = new FileOutputStream(newFilePath);
            Workbook workBook = PoiExcelUtil.getWorkBook(file);
            //保存信息
            this.saveFileToMongo(workBook, wxFile.getId());
            //进行计算
//            this.countFileAndSave(workBook, out, wxFile);
            
            return true;
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            String exceptionString = SQStringUtil.getExceptionString(e);
            wxFile.setSendException(sheetName + "###" + exceptionString);
            wxFile.setFileStatus(WxConstants.FILE_STATUS_COUNT_E);
            this.updateById(wxFile);
        } finally {
            if(null != out) {
                try {
                    out.close();
                } catch (IOException e) {
                    log.error(e.getMessage(), e);
                }
            }
        }
        
        return false;
    }

    private void saveFileToMongo(Workbook workBook, String fileId) throws Exception{
        List<String> sheetNames = CollUtil.newArrayList("个人当天", "个人累计", "众包宽带");
        List<String> headerIndexs = CollUtil.newArrayList("0", "1", "1 2,2");
        
        this.readSaveWorkbook(workBook, sheetNames, fileId, headerIndexs);
    }

    private void readSaveWorkbook(Workbook workBook, List<String> sheetNames, String fileId, List<String> headerIndexs) {
        for (int i = 0; i < sheetNames.size(); i++) {
            String sheetName = sheetNames.get(i);
            String headerIndexStr = headerIndexs.get(i);
            List<String> headerSplit = StrUtil.split(headerIndexStr, ",");
            Integer headerIndex = null;
            
            Sheet sheet = workBook.getSheet(sheetName);
            Map<Integer, String> titleMap = MapUtil.newHashMap();
            if(CollUtil.size(headerSplit) == 1) {
                headerIndex = Integer.valueOf(headerIndexStr);
                Row headerRow = sheet.getRow(headerIndex);
                for(Iterator<Cell> titleIterator = headerRow.cellIterator(); titleIterator.hasNext();) {
                    Cell next = titleIterator.next();
                    titleMap.put(next.getColumnIndex(), PoiExcelUtil.getCellValue(next));
                }
            } else {
                int maxHeaderIndex = 0;
                int maxColIndex = 0;
                for (String string : headerSplit) {
                    List<String> split = StrUtil.split(string, " ");
                    String rowIndexStr = split.get(0);
                    Integer rowIndex = Integer.valueOf(rowIndexStr);
                    maxHeaderIndex = rowIndex;
                    Row headerRow = sheet.getRow(rowIndex);
                    int headerCellCount = 0;
                    if(CollUtil.size(split) > 1) {
                        headerCellCount = Integer.valueOf(split.get(1));
                    } else {
                        headerCellCount = headerRow.getLastCellNum();
                    }
                    for(int j = maxColIndex; j <= headerCellCount; j++) {
                        Cell cell = headerRow.getCell(j);
                        if(null == cell) {
                            continue;
                        }
                        titleMap.put(cell.getColumnIndex(), PoiExcelUtil.getCellValue(cell));
                    }
                    maxColIndex = headerCellCount + 1;
                }
                
                
                headerIndex = maxHeaderIndex;
            }
            
            for (int j = headerIndex + 1; j <= sheet.getLastRowNum(); j++) {
                Row row = sheet.getRow(j);
                Map<String, Object> map = MapUtil.newHashMap();
                WorkExcelInfo excelInfo = new WorkExcelInfo();
                excelInfo.setId(IdUtil.getSnowflakeNextIdStr());
                excelInfo.setCreateTime(DateUtil.date());
                excelInfo.setFileId(fileId);
                excelInfo.setSheetName(sheetName);
                excelInfo.setRowInfo(map);
                for (Iterator<Cell> iterator = row.cellIterator(); iterator.hasNext();) {
                    Cell cell = iterator.next();
                    if(cell == null) {
                        continue;
                    }
                    int columnIndex = cell.getColumnIndex();
                    Object value = null;
                    String title = titleMap.get(Integer.valueOf(columnIndex));
                    CellType cellType = cell.getCellType();
                    switch (cellType) {
                    case NUMERIC:
                        value = cell.getNumericCellValue();
                        break;
                    case STRING:
                        value = cell.getStringCellValue();
                        break;
                    case BOOLEAN:
                        value = cell.getBooleanCellValue();
                        break;
                    case FORMULA:
                        value = cell.getCellFormula();
                        break;
                    case BLANK:
                        value = StrUtil.EMPTY;
                        break;
                    case ERROR:
                        break;
                    default:
                        value = StrUtil.EMPTY;
                        break;
                    }
                    map.put(title, value);
                }
                Object timeObject = map.get("时间");
                if(null != timeObject) {
                    try {
                        Double timeNum = Double.valueOf(timeObject.toString());
                        Date date = PoiExcelUtil.excelDateNumToDate(timeNum);
                        excelInfo.setInfoDate(date);
                    } catch (Exception e) {
                        log.error(e.getMessage(), e);
                    }        
                }
                JSONObject infoObject = JSONObject.from(map);
                String userNo = infoObject.getString("会员账号");
                String opNo = infoObject.getString("OP账号");
                String workNo = infoObject.getString("工号");
                WorkUser workUser = null;
                if(null != userNo) {
                    workUser = this.workUserAccountManager.getUserByUserAccount(userNo);
                }
                if(null == workUser && null != opNo) {
                    workUser = this.workUserAccountManager.getUserByOpCode(opNo);
                }
                if(null == workUser && null != workNo) {
                    workUser = this.workUserAccountManager.getUserByOpCode(workNo);
                    if(null == workUser) {
                        workUser = this.workUserAccountManager.getUserByUserAccount(workNo);
                    }
                }
                if(null != workUser) {
                    excelInfo.setUserId(workUser.getId());
                }
                excelInfoManager.saveInfo(excelInfo);
            } 
        }
    }
    
    /**
     * @Description 根据文件内容计算员工绩效
     * @param workBook
     * @param out
     * @param id
     * Create at: 2025-10-22 15:52:05
     * @author: duany
     * Revision:
     *    2025-10-22 15:52:05 - first revision by duany
     *        
     */
    private void countFileAndSave(Workbook workBook, FileOutputStream out, WxFile file) {
        // TODO
        String separator = "---";
        String workplaceId = file.getWorkplaceId();
        List<WorkUser> workUserList = this.workUserManager.getUserByWorkplace(workplaceId);
        List<String> userIdList = workUserList.stream().map(WorkUser::getId).collect(Collectors.toList());
        List<WorkUserAccount> accountList = workUserAccountManager.listByUserIds(userIdList);
        String fileId = file.getId();
        List<WorkExcelInfo> infoList = excelInfoManager.getByFileIdSheetName(fileId, "个人当天");
        Map<String, WorkUser> opWorkUserMap = MapUtil.newHashMap();
        Map<String, WorkUser> accountWorkUserMap = MapUtil.newHashMap();
        List<UserWorkInfo> m1List = CollUtil.newArrayList();
        List<UserWorkInfo> m23List = CollUtil.newArrayList();
        List<UserWorkInfo> m4List = CollUtil.newArrayList();
        List<String> m23IdList = CollUtil.newArrayList();
        List<String> m4IdList = CollUtil.newArrayList();
        Set<String> userExcelSet = CollUtil.newHashSet();
        for (WorkExcelInfo workExcelInfo : infoList) {
            String userId = workExcelInfo.getUserId();
            Map<String, Object> rowInfo = workExcelInfo.getRowInfo();
            JSONObject infoObject = JSONObject.from(rowInfo);
            WorkUser workUser = this.workUserManager.getById(userId);
            String userRealName = StrUtil.EMPTY;
            if(null != workUser) {
                userRealName = workUser.getUserRealName();
                Long infoTimeNum = infoObject.getLong("时间");
                if(null == infoTimeNum) {
                    continue;
                }
                //日期
                Date infoTime = PoiExcelUtil.excelDateNumToDate(Double.valueOf(infoTimeNum.toString()));
                //入职日期
                Date hireDate = workUser.getHireDate();
                //计算员工级别
                String infoLevel = workUserManager.getWorkUserTempLevel(hireDate, infoTime);
                String key = userId + separator + infoLevel;
                userExcelSet.add(key);
            }
        }
        for (String userIdLevel : userExcelSet) {
            List<String> split = StrUtil.split(userIdLevel, separator);
            if("M4".equals(split.get(1))) {
                m4IdList.add(userIdLevel);
            } else {
                m23IdList.add(userIdLevel);
            }
        }
        //单元格格式
        Font font = PoiExcelUtil.getFont(workBook, "微软雅黑", Boolean.FALSE, null, Short.valueOf("9"));
        CellStyle cellStyle = PoiExcelUtil.getCellStyle(workBook, BorderStyle.THIN, null,
                font, HorizontalAlignment.CENTER, VerticalAlignment.CENTER, Boolean.TRUE);
        //创建sheet及标题栏
        Sheet m4sheet = workBook.createSheet("M4");
        this.createTitle(m4sheet, workBook);
        Sheet m23sheet = workBook.createSheet("M2-M3");
        this.createTitle(m23sheet, workBook);
        int m4rowNum = 4;
        int m23rowNum = 4;
        //遍历并赋值
        Date date = DateUtil.beginOfDay(DateUtil.date());//当前日期
        //获取接线量级别
        List<WorkStepLevel> levels = this.workStepLevelPriceManager.queryLevels();
        Map<Long, WorkStepLevel> levelMap = SQMapUtil.ListToMap(levels, WorkStepLevel::getLevelSequence);
        List<WorkStepLevelPrice> stepLevelPrices = this.workStepLevelPriceManager
                .queryByLevelIds(levels.stream().map(WorkStepLevel::getId).collect(Collectors.toList()));
        Map<String, List<WorkStepLevelPrice>> stepLevelPricesMap = SQMapUtil.ListToMapList(stepLevelPrices,
                WorkStepLevelPrice::getLevelId);
        for (String userIdLevel : m4IdList) {
            Row row = m4sheet.createRow(m4rowNum);
            //计算起止时间
            Date beginDate = DateUtil.beginOfMonth(date);
            Date endDate = date;
            
            m4rowNum ++;
        }
        for (String userIdLevel : m23IdList) {
            List<String> splitTrim = StrUtil.splitTrim(userIdLevel, separator);
            String userId = splitTrim.get(0);
            String level = splitTrim.get(1);
            //获得员工信息
            WorkUser workUser = this.workUserManager.getById(userId);
            WorkUserAccount userAccount = this.workUserAccountManager.getUserAccountByUserId(userId);
            //计算起止时间
            Date hireDate = workUser.getHireDate();
            Date beginDate = null;
            Date endDate = null;
            List<Date> userDateList = this.workUserManager.getUserLevelDate(hireDate);
            if ("M1".equals(level)) {
                beginDate = hireDate;
                Date m1EndDate = userDateList.get(1);
                if(date.compareTo(m1EndDate) >= 0) {
                    endDate = m1EndDate;
                } else {
                    endDate = date;
                }
            } else if ("M2".equals(level)) {
                beginDate = userDateList.get(2);
                Date m2EndDate = userDateList.get(3);
                if(date.compareTo(m2EndDate) >= 0) {
                    endDate = m2EndDate;
                } else {
                    endDate = date;
                }
            } else if ("M3".equals(level)) {
                beginDate = userDateList.get(4);
                Date m2EndDate = userDateList.get(5);
                if(date.compareTo(m2EndDate) >= 0) {
                    endDate = m2EndDate;
                } else {
                    endDate = date;
                }
            }
            //基础接线量指标数
            Integer maxCount = 0;
            Integer minCount = 0;
            WorkScoreConf scoreConf = workIWorkScoreConfManager.getByCode(m2m3Code);
            String confInfo = scoreConf.getConfInfo();
            List<String> confInfos = StrUtil.splitTrim(confInfo, "-");
            minCount = Integer.valueOf(confInfos.get(0));
            maxCount = Integer.valueOf(confInfos.get(1));
            BigDecimal highestScore = scoreConf.getHighestScore();
            BigDecimal lowestScore = scoreConf.getLowestScore();
            
            
            List<WorkExcelInfo> workInfoList = excelInfoManager.getByUser(userId, beginDate, endDate, "个人当天");
            Row row = m23sheet.createRow(m23rowNum);
            //姓名工号
            Cell nameCell = row.createCell(0);
            Cell noCell = row.createCell(1);
            Cell opCell = row.createCell(2);
            PoiExcelUtil.setStringCellValue(noCell, userAccount.getUserAccount(), cellStyle);//员工编号
            PoiExcelUtil.setStringCellValue(opCell, userAccount.getUserOpCode(), cellStyle);//工号
            PoiExcelUtil.setStringCellValue(nameCell, workUser.getUserRealName(), cellStyle);//姓名
            //基础指标项计算
            //有效通话量
            BigDecimal numTotalCount = BigDecimal.ZERO;
            for (WorkExcelInfo info : workInfoList) {
                Map<String, Object> rowInfo = info.getRowInfo();
                JSONObject infoObject = JSONObject.from(rowInfo);
                BigDecimal manualCallVolume = infoObject.getBigDecimal("有效人工通话量");
                if(null != manualCallVolume) {
                    BigDecimal bigDecimal = numTotalCount.add(manualCallVolume);
                    numTotalCount = bigDecimal;
                }
            }
            //通话评分
            BigDecimal totalCountScore = this.getUserScore(this.m2m3Code, numTotalCount, 2);
            //尊时率
            String respectTime = StrUtil.EMPTY;
            //尊时率打分
            String respectTimeScore = StrUtil.EMPTY;
            
            
            //基础指标项赋值
            //有效人工通话量
            Cell manualCallVolumeCell = row.createCell(3);
            PoiExcelUtil.setNumberCellValue(manualCallVolumeCell, numTotalCount, cellStyle);
            //通话评分
            Cell totalCountScoreCell = row.createCell(4);
            PoiExcelUtil.setNumberCellValue(totalCountScoreCell, totalCountScore, cellStyle);
            //尊时率
            Cell respectTimeCell  = row.createCell(5);
            PoiExcelUtil.setStringCellValue(respectTimeCell, respectTime, cellStyle);
            //尊时率打分
            Cell respectTimeScoreCell = row.createCell(6);
            PoiExcelUtil.setStringCellValue(respectTimeScoreCell, respectTimeScore, cellStyle);
            row.createCell(7);
            row.createCell(8);
            row.createCell(9);
            row.createCell(10);
            row.createCell(11);
            row.createCell(12);
            row.createCell(13);
            row.createCell(14);
            
            m4rowNum ++;
        }
    }
    
    /**
     * @Description 创建标题行
     * @param sheet
     * Create at: 2025-10-29 16:01:46
     * @author: duany
     * Revision:
     *    2025-10-29 16:01:46 - first revision by duany
     *        
     */
    private Sheet createTitle(Sheet sheet, Workbook wb) {
        Row row1 = sheet.createRow(0);
        Row row2 = sheet.createRow(1);
        Row row3 = sheet.createRow(2);
        Row row4 = sheet.createRow(3);
        // 设置行高
        row1.setHeightInPoints(20);
        row2.setHeightInPoints(20);
        row3.setHeightInPoints(35);
        row4.setHeightInPoints(80);
        // 设置列宽
        sheet.setColumnWidth(0, 2500);
        sheet.setColumnWidth(1, 2500);
        sheet.setColumnWidth(2, 2000);

        Cell nameTitleCell = row1.createCell(0);
        nameTitleCell.setCellValue("各项系数考核得分核算");
        // 合并单元格：用户信息头 从第1行第1列到第3行第3列
        PoiExcelUtil.getCellRangeAddress(sheet, 0, 2, 0, 2, BorderStyle.THIN);
        // 设置字体
        Font nameTitleCellFont = PoiExcelUtil.getFont(wb, "仿宋", Boolean.TRUE, null, Short.valueOf((short) 17));
        // 设置单元格格式
        CellStyle nameTitleCellStyle = PoiExcelUtil.getCellStyle(wb, BorderStyle.THIN, IndexedColors.LIGHT_BLUE.index,
                nameTitleCellFont, HorizontalAlignment.CENTER, VerticalAlignment.CENTER, Boolean.TRUE);
        nameTitleCell.setCellStyle(nameTitleCellStyle);

        // 设置标题字体
        Font titleBoldCellFont = PoiExcelUtil.getFont(wb, "宋体", Boolean.TRUE, null, Short.valueOf((short) 10));
        Font titleCellFont = PoiExcelUtil.getFont(wb, "宋体", Boolean.FALSE, null, Short.valueOf((short) 10));
        Font redBoldCellFont = PoiExcelUtil.getFont(wb, "宋体", Boolean.TRUE, IndexedColors.RED1.index,
                Short.valueOf((short) 10));

        // 标题单元格格式
        CellStyle userTitleStyle = PoiExcelUtil.getCellStyle(wb, BorderStyle.THIN, IndexedColors.LIGHT_ORANGE.index,
                titleBoldCellFont, HorizontalAlignment.CENTER, VerticalAlignment.CENTER, Boolean.TRUE);

        // 员工信息标题栏
        Cell accountCell = row4.createCell(0);
        Cell opCell = row4.createCell(1);
        Cell nameCell = row4.createCell(2);
        PoiExcelUtil.setStringCellValue(accountCell, "员工编号", userTitleStyle);
        PoiExcelUtil.setStringCellValue(opCell, "工号", userTitleStyle);
        PoiExcelUtil.setStringCellValue(nameCell, "姓名", userTitleStyle);

        // 基础指标标题
        Cell baseTitleCell = row1.createCell(3);
        // 合并单元格
        PoiExcelUtil.getCellRangeAddress(sheet, 0, 0, 3, 14, BorderStyle.THIN);
        // 单元格格式
        CellStyle baseTitleStyle = PoiExcelUtil.getCellStyle(wb, BorderStyle.THIN, IndexedColors.TURQUOISE.index,
                titleCellFont, HorizontalAlignment.CENTER, VerticalAlignment.CENTER, Boolean.TRUE);
        PoiExcelUtil.setStringCellValue(baseTitleCell, "基础接续指标考核", baseTitleStyle);
        // 子标题
        Cell baseProCell = row2.createCell(3);
        Cell baseSerCell = row2.createCell(6);
        // 合并单元格
        PoiExcelUtil.getCellRangeAddress(sheet, 1, 1, 3, 5, BorderStyle.THIN);
        PoiExcelUtil.getCellRangeAddress(sheet, 1, 1, 6, 14, BorderStyle.THIN);
        // 赋值
        PoiExcelUtil.setStringCellValue(baseProCell, "生产能力（20分）", baseTitleStyle);
        PoiExcelUtil.setStringCellValue(baseSerCell, "服务能力（40分）", baseTitleStyle);
        // 总考核分数60分
        Cell totalNumCell = row3.createCell(14);
        CellStyle totalNumStyle = wb.createCellStyle();
        totalNumStyle = PoiExcelUtil.getCellStyle(wb, BorderStyle.THIN, IndexedColors.TURQUOISE.index, redBoldCellFont,
                HorizontalAlignment.CENTER, VerticalAlignment.CENTER, Boolean.TRUE);
        PoiExcelUtil.setStringCellValue(totalNumCell, "总考核分数60分", totalNumStyle);
        // 有效通话量 得分(0-10)
        Cell callColumeCell = row4.createCell(3);
        Cell callColumeScoringCell = row4.createCell(4);
        CellStyle callColumeCellStyle = PoiExcelUtil.getCellStyle(wb, BorderStyle.THIN, IndexedColors.DARK_TEAL.index,
                titleBoldCellFont, HorizontalAlignment.CENTER, VerticalAlignment.CENTER, Boolean.TRUE);
        PoiExcelUtil.setStringCellValue(callColumeCell, "有效通话量", callColumeCellStyle);
        PoiExcelUtil.setStringCellValue(callColumeScoringCell, "得分(0-10)", callColumeCellStyle);
        // 遵时率 得分(0-10)
        Cell onTimeCell = row4.createCell(5);
        Cell onTimeScoringCell = row4.createCell(6);
        CellStyle onTimeCellStyle = PoiExcelUtil.getCellStyle(wb, BorderStyle.THIN, IndexedColors.LEMON_CHIFFON.index,
                titleBoldCellFont, HorizontalAlignment.CENTER, VerticalAlignment.CENTER, Boolean.TRUE);
        PoiExcelUtil.setStringCellValue(onTimeCell, "遵时率", onTimeCellStyle);
        PoiExcelUtil.setStringCellValue(onTimeScoringCell, "得分(0-10)", onTimeCellStyle);
        // 问题解决情况满意度值（%） 得分(0；7-15)
        Cell satisfactionCell = row4.createCell(7);
        Cell satisfactionScoringCell = row4.createCell(8);
        CellStyle satisfactionCellStyle = PoiExcelUtil.getCellStyle(wb, BorderStyle.THIN, IndexedColors.TEAL.index,
                titleBoldCellFont, HorizontalAlignment.CENTER, VerticalAlignment.CENTER, Boolean.TRUE);
        PoiExcelUtil.setStringCellValue(satisfactionCell, "问题解决情况满意度值（%）", satisfactionCellStyle);
        PoiExcelUtil.setStringCellValue(satisfactionScoringCell, "得分(0；7-15)", satisfactionCellStyle);
        // 参评率 得分(0；4-10)
        Cell evaluationCell = row4.createCell(9);
        Cell evaluationScoringCell = row4.createCell(10);
        CellStyle evaluationCellStyle = PoiExcelUtil.getCellStyle(wb, BorderStyle.THIN,
                IndexedColors.CORAL.index, titleBoldCellFont, HorizontalAlignment.CENTER,
                VerticalAlignment.CENTER, Boolean.TRUE);
        PoiExcelUtil.setStringCellValue(evaluationCell, "参评率", evaluationCellStyle);
        PoiExcelUtil.setStringCellValue(evaluationScoringCell, "得分(0；4-10)", evaluationCellStyle);
        // 一次解决率（%） 得分（0；5-10）
        Cell solveCell = row4.createCell(11);
        Cell solveScoringCell = row4.createCell(12);
        CellStyle solveCellStyle = PoiExcelUtil.getCellStyle(wb, BorderStyle.THIN, IndexedColors.LIGHT_GREEN.index,
                titleBoldCellFont, HorizontalAlignment.CENTER, VerticalAlignment.CENTER, Boolean.TRUE);
        PoiExcelUtil.setStringCellValue(solveCell, "一次解决率（%）", solveCellStyle);
        PoiExcelUtil.setStringCellValue(solveScoringCell, "得分（0；5-10）", solveCellStyle);
        // 立单率/提单率 得分（0；2-5）
        Cell orderCell = row4.createCell(13);
        Cell orderScoringCell = row4.createCell(14);
        CellStyle orderCellStyle = PoiExcelUtil.getCellStyle(wb, BorderStyle.THIN, IndexedColors.PLUM.index,
                titleBoldCellFont, HorizontalAlignment.CENTER, VerticalAlignment.CENTER, Boolean.TRUE);
        PoiExcelUtil.setStringCellValue(orderCell, "立单率/提单率", orderCellStyle);
        PoiExcelUtil.setStringCellValue(orderScoringCell, "得分（0；2-5）", orderCellStyle);
        // 基础考核总得分
        Cell basTotalCell = row4.createCell(13);
        CellStyle basTotalCellStyle = PoiExcelUtil.getCellStyle(wb, BorderStyle.THIN, IndexedColors.LIGHT_YELLOW.index,
                titleBoldCellFont, HorizontalAlignment.CENTER, VerticalAlignment.CENTER, Boolean.TRUE);
        PoiExcelUtil.setStringCellValue(basTotalCell, "基础考核总得分", basTotalCellStyle);
        
        //接线量量酬
        Cell callVolumeTotalcell = row1.createCell(15);
        // 标题单元格格式
        CellStyle callVolumeTotalStyle = PoiExcelUtil.getCellStyle(wb, BorderStyle.THIN, IndexedColors.YELLOW1.index,
                titleCellFont, HorizontalAlignment.CENTER, VerticalAlignment.CENTER, Boolean.TRUE);
        //合并单元格
        PoiExcelUtil.getCellRangeAddress(sheet, 0, 2, 15, 19, BorderStyle.THIN);
        PoiExcelUtil.setStringCellValue(callVolumeTotalcell, "接线量量酬", callVolumeTotalStyle);
        //认证会员接线量（有效通话量）考核系数最低0.7  8月应出勤天数 对应首月出勤天数    基本量 提成
        Cell memberCountCell = row4.createCell(15);
        Cell scheduledWorkingDaysCell = row4.createCell(16);
        Cell workingDaysCell = row4.createCell(17);
        Cell basicQuantityCell = row4.createCell(18);
        Cell commissionCell = row4.createCell(19);
        CellStyle callVolumeCellStyle = PoiExcelUtil.getCellStyle(wb, BorderStyle.THIN, IndexedColors.PALE_BLUE.index,
                titleBoldCellFont, HorizontalAlignment.CENTER, VerticalAlignment.CENTER, Boolean.TRUE);
        PoiExcelUtil.setStringCellValue(memberCountCell, "认证会员", callVolumeCellStyle);
        PoiExcelUtil.setStringCellValue(scheduledWorkingDaysCell, "应出勤天数", callVolumeCellStyle);
        PoiExcelUtil.setStringCellValue(workingDaysCell, "对应首月出勤天数", callVolumeCellStyle);
        PoiExcelUtil.setStringCellValue(basicQuantityCell, "基本量", callVolumeCellStyle);
        PoiExcelUtil.setStringCellValue(commissionCell, "提成", callVolumeCellStyle);
        
        return sheet;
    }
    
    public BigDecimal getUserScore(WorkScoreConf scoreConf, BigDecimal account, Integer scale) {
        if (null == scoreConf) {
            return null;
        }
        String confCode = scoreConf.getConfCode();
        BigDecimal highestScore = scoreConf.getHighestScore();
        BigDecimal lowestScore = scoreConf.getLowestScore();
        String confInfo = scoreConf.getConfInfo();
        BigDecimal result = BigDecimal.ZERO;
        if (StrUtil.equals("hubei_base_volume_m2m3", confCode)) {
            List<String> split = StrUtil.split(confInfo, "-");
            Integer maxCount = Integer.valueOf(split.get(1));
            Integer minCount = Integer.valueOf(split.get(0));
            Integer difference = maxCount - minCount;
            BigDecimal deScore = highestScore.subtract(lowestScore);
            if (account.compareTo(new BigDecimal(minCount)) <= 0) {
                result = lowestScore;
            } else if (account.compareTo(new BigDecimal(maxCount)) >= 0) {
                result = highestScore;
            } else {
                account = account.subtract(new BigDecimal(minCount));
                int devideScale = 4;
                if (scale != null) {
                    devideScale = scale + 2;
                }
                BigDecimal differenceDecimal = new BigDecimal(difference);
                BigDecimal divide = account.divide(differenceDecimal, devideScale, RoundingMode.HALF_UP);
                result = deScore.multiply(divide);
            }
        }
        if (null != scale) {
            result = result.setScale(scale.intValue(), RoundingMode.HALF_UP);
        }
        return result;
    }
    
    public BigDecimal getUserScore(String confCode, BigDecimal account, Integer scale) {
        WorkScoreConf workScoreConf = this.workIWorkScoreConfManager.getByCode(confCode);
        return getUserScore(workScoreConf, account, scale);
    }
}
